Risc World 3/Risc World 3.iso/SOFTWARE/ISSUE3/POWERBASE/DOCS/Ch04-Creat

Text File | 2001-01-31 | 41KB | 789 lines

==============================
Ch 4 − Creating a New Database
==============================

Five steps are involved in setting up a new database:

(1)     Create the database application shell.
(2)     Design the record layout.
(3)     Specify the number of records the database is to contain.
(4)     Specify the primary key.
(5)     Build the empty database.

4.1     Creating the database application shell
-----------------------------------------------
You need to have Powerbase installed on the iconbar but with no database
open, i.e. “No data” should appear under the icon. Click SELECT over the
icon and a Save box will appear containing the default name !Database. Type
in the name of your database and drag the icon to a directory window.
Remember that, for RISC OS versions prior to 4.00, the name cannot exceed 10
characters, including the initial “!”. If you enter more the name will be
truncated and you could end up overwriting an existing database with a
similar name. You don’t actually need to enter the “!”; Powerbase will
insert it automatically.

If you open the newly-created application directory (Shift double-click) you
will find that it contains four files (!Run, !Sprites, !Sprites22 and Data)
and five directories (Indices, Menus, PrintJobs, PrintRes and ValTables).
All Powerbase databases expect these objects to be present so don’t delete
any of them.

4.2     Designing the record layout
-----------------------------------
When you created the application shell you were left with a window on
screen, blank apart from a grid of blue lines, and it is here that you must
design the database record. This is the lengthiest part of setting up a
database, although efforts have been made to render it as easy as possible.
If you have closed the window just click SELECT on the Powerbase icon on the
iconbar to re-open it.

4.2.1   Simple field creation

Clicking MENU over the window brings up the New database menu on which every
item except Create field and Grid is shaded at this stage. Choosing Create
field displays the Field Definition window which lets you specify the
characteristics of a field. Grid allows you to choose options for the grid
used to lay out the fields. This grid appears only at the design stage, not
on a working database (except when using the Adjust format and New record
format features). It may be turned off but you will probably find it a help
in getting the field layout as you want it.  There are options to choose the
colour of the grid, whether it is represented by solid or dotted lines and
what the spacing between the lines is.  By default the grid has solid, light
blue lines spaced 32 OS units apart and  fields will “snap” to it at
intervals of 4 OS units.  The snap interval may be altered or the snapping
disabled.  First decide what class of field you want to create. There are
eight such classes, selected via radio buttons:

(1)     Editable        (5)     Computed
(2)     Scrollable list (6)     Stamp
(3)     Check-box       (7)     Keypad button
(4)     External        (8)     Extra button

For the present we will confine ourselves to the first, which is the default
selection. As well as the field class you must decide on the type of field
within the class. The default offered is Unrestricted, meaning that it will
accept all printable characters. We’ll look at other types later.

Decide on a name for the field and enter it in the Descriptor icon. This is
the name which will appear on the record window and may be up to 40
characters long. You must also enter a Tag, which is used to identify the
field in search formulae and is limited to 4 characters. Next enter the Data
length; the maximum number of characters the field is to hold. Values up to
246 are allowed. Now click on Create and the field will appear on the record
window. It’s probably not where you want it so use SELECT to drag the white
rectangle to the position required. When you drop the field in its new
position the descriptor falls into place too. If you want the descriptor
somewhere other than to the left of the data icon (above it, for example)
move it by itself after positioning the data icon.  For fine adjustment
re-open the Create window by double-clicking on the field and nudge the
field into position using the bump icons at the bottom left corner.

                                - 27 -

To edit an existing field you can either click MENU over the field and
choose Edit field or simply double-click SELECT over the field itself as
above.  You can also display the data for any field by clicking MENU
anywhere in the window and choosing the field from the Fields Created
submenu or by clicking the List fields button on the open dialogue box.
After making changes click on Update (Create will be shaded).

4.2.2   Deleting, inserting and re-ordering fields.

It is important to understand the difference between the physical position
of fields on the screen and the internal numbering of the fields. The former
is purely a matter of appearance and you may drag the fields about the
screen to your heart’s content, but the latter is fundamental to the way the
database will function.  Each field has a number which corresponds to the
order in which it was created.  Whenever you invoke the Field Definition
window the title-bar shows either the number of the (new) field you are
about to create (e.g. “New field 3”) or the number of the (existing) field
you were pointing at when you opened the menu (e.g. “Modify field 5”).  The
numbering of fields determines the order in which they will be “visited” by
the caret when you are actually using the database and typing Return to get
from field to field, i.e. the editing order.

A field may be deleted by bringing it up for editing as described above and
clicking on Remove field. Fields which come after the deleted field will
then be found to have had their field-numbers reduced by 1.  Inserting a
field into the middle of the existing field-sequence is obviously a little
more complicated because we have to specify where in the sequence the new
field goes.  You need to know the number of the field which you want to
follow the new one. This number is entered in the before icon before you
click on Create. The layout of this part of the window should make things
clear: “Create before <n>”. If you examine fields which come after the
insertion you will see that their numbers have increased by 1.

You can change the numbering of a field by removing it and then re-inserting
it, but  there is a better way.  Bring the field up for editing as
previously described. We will call this the “current field”. Enter the
number of another existing field in the same place as was used above to
specify the insertion position of a new field. We’ll call this the “entered
field”. You may then do one of the following:

•       Click Swap with. This does exactly what you would expect. It swaps
        the positions of the current field and the entered field in the
        field sequence (but not their placement on-screen).

•       Click Renumber as.  This is a bit more complicated. The current
        field acquires the number of the entered field. If this involves
        giving the current field a lower number than previously the fields
        beyond the new position are all moved up one place to open a gap for
        it, at the same time closing up the gap left by moving the current
        field from its old position. If it is being given a higher number
        the fields above its old position all move down one place, closing
        up the gap left by its removal and opening a gap in the required
        place farther up the sequence. (Once again, the physical position of
        the fields on the window is unchanged.)

4.2.3   Moving and re-sizing the bounding box

Left to itself Powerbase makes all its field icons the same height and of a
suitable length to contain the number of characters specified in Data
length. This may not be quite what you want. If the data length is large the
field could run off the right edge of the window. You might also want to
make the field taller for emphasis. (But not for displaying multiple lines.
Only fields of Text block type can be multi-line. These are discussed
later.)

We have already seen how you can alter the position of a field by dragging
with SELECT. By dragging at an edge with ADJUST you can change the size of
the bounding box.

You can also specify both size and position by entering the required width
and height of the bounding-box in the W and H icons and the co-ordinates of
the lower-left corner in the X and Y icons of the Field creation window. All
these values are in OS units (the same units as are used for plotting to the
screen) but the origin is the top left corner of the record window. This
means that the Y values are always negative.

Clicking on Fit automatically sets the bounding-box width to fit the data
length.

4.2.4   More about tags and descriptors

Tags are very important in Powerbase. They are used when querying the
database to produce reports, export CSV files etc. and also by some internal
operations. Every printable field (see 3.3) must have a tag and no two  tags
can be the same. Descriptors are less important. They are there to provide
visible labels for fields and in some cases you may not need one at all.
e.g. You might want the record to look like this:

                                - 28 -

           NAME Fred Bloggs
        ADDRESS 27, Every Street
                Anytown
                Woolshire
                WL4 7XZ

There are 5 fields here but, having given the second one the descriptor
ADDRESS, you don’t really want descriptors for the remaining 3 (except
perhaps POSTCODE for the last). It is quite in order to have null
descriptors like this, but your must give each field a tag. Suitable ones
might be NAME, ADD1, ADD2, ADD3, CODE. (Remember each must be unique and not
more than 4 characters.)

You can omit the tag where the Data length is 0. This allows you to create
fields which are simply explanatory labels. Since there is no data in them
there would be no reason to include them in a query.

4.2.5   Other types of Editable field

So far we have only used fields of Unrestricted type. Clicking on bump icons
to the left of the field type, or on the menu button to the right, lets you
cycle through the various types available. These are:

(a)     Unrestricted    Accepts any printable character.

(b)     Alphanumeric    Accepts all letters and numerals and common
                        punctuation.

(c)     Upper case      Accepts capital letters and numerals only.

(d)     Numeric Accepts numerals, +, − and . (decimal point).

(e)     Yes/No/Maybe    Accepts Y, N and ? only.

(f)     Date            Accepts dates in the form dd-mm-yy or dd-mm-yyyy,
                        checking that the date is valid and reporting an
                        error otherwise.

(g)     Time            Accepts times in the form hh:mm:ss up to a value of
                        23:59:59. The time is checked for validity and
                        errors are reported.

(h)     Internet        Special field for email addresses and web URLs.
                        There is no restriction on character input, but
                        double-clicking will call up your mail program
                        or browser (if it has been “seen” by the filer) with
                        the address loaded.

When Numeric is selected, certain icons in the dialogue box which are
normally shaded become available. Thus, you can specify floating-point,
fixed-point or integer format by means of a group of radio buttons. You may
also specify a maximum and minimum value for numbers which may be entered in
these fields. The Numeric min icon is also used to hold the starting value
for Sequence number fields (see 4.2.10).

A Date field should be either 8 or 10 characters long in order to hold the
date in one of the two formats specified above. The hyphen separator in
these dates may be changed via the Preferences window (see 14.5.1).
Powerbase is very tolerant of the way you actually enter a date. You may
type any non-numeric character as a separator: Powerbase will make sense of
an entry such as 4/5/87, converting it to 04-05-87 when you type Return.
Arithmetic may be performed on dates held in this type of field, e.g. you
could have a Calculated field subtract the contents of two Date fields and
display the difference in days (see 6.1.5).

Time fields also allow flexibility in how you enter the values. If you enter
3.45;9 it will be reformatted as 03:45:09. The colon separator may be
changed via the Preferences window (see 14.5.1). You may also enter
incomplete times which are, by default, interpreted as follows. A number
entered on its own is treated as hours. Thus, if you enter 6 and type Return
it will be reformatted as 06:00:00. Two number separated by a non-numeric
character are treated as hours and minutes, e.g. 6/5 would be reformatted as
06:05:00. You may edit Powerbase’s Config file to reverse this behaviour so
that 6 is formatted as 00:00:06 and 6/5 as 00:05:06. When using this mode of
entry you may also suppress the hours part of the display for values less
than an hour, e.g. so that 6/5 is formatted as 05:06. (See 14.6 for editing
Config.)

Like Date fields, Time fields may be included in calculations, e.g. to
obtain the difference in seconds between two times or to average a number of
times (see 6.1.4).

4.2.6   Scrollable lists

These are also user-editable but are treated separately because they are
like no other editable field.  They are intended for record structures which
contain closely-related data whose number might vary widely from one record
to another. A music CD, for example, might have only 3 or 4 tracks but it
could have 30 or more. If you are cataloguing CDs and including track
information you don’t really want to define 30 separate fields to allow for
just a few extreme records − and find even then that the odd disc has more
than 30 tracks! A scrollable list lets you make allowance for a modest
number of items, adding new ones as required for individual records. Since

                                - 29 -

the list is scrollable it occupies no more space on the record window no
matter how many items are in the list.

You may create this type of field with anywhere from 1-4 columns. The value
entered in Data length is the column width, i.e. the number of characters
which each cell can hold. All columns are normally of the same width (but
see below for how to specify differing widths). When you click Create all
that appears is a plain white rectangle: not until you complete the process
of creating the database does it appear with the cells and vertical
scroll-bar characteristic of this type of field. The height of this
rectangle will determine the height, and hence the number of visible rows,
in the list. By default it will accommodate 3 rows but you may drag with
ADJUST to make it higher. When you release the mouse button the rectangle
will snap to a height which exactly fits the nearest whole number of rows.

As noted above, you can make the columns differ in width. The field is
created as above and the other steps to produce a working database are
carried out. You then need to create a little text file called Format
containing the individual column widths, one to a line. There must be as
many numbers as columns and the sum of the new column widths must be exactly
the same as it was before. Suppose you had created a 3-column scrollable
list with a Data length of 10, giving 3 columns, each capable of holding 10
characters. This means you have 30 characters to play with. You can put into
Format the numbers 5, 15, 10 or 3, 7, 20 or any other 3 numbers which add up
to 30. Where do you put the Format file? Open the database directory
(Shift-double-click) and look for a subdirectory called Scroll<n>, where n
is the field number of the scrollable list. Format should go in this
subdirectory.

The data contained in scrollable lists is not held in the Database file.
Data for each record is in a separate file stored in a special system of
subdirectories inside the database directory. In this they resemble External
fields (see 4.2.8)

4.2.7   Check-box fields

These are fields whose status changes when clicked on with SELECT. Five
types are defined:

(a)     Cross/tick      Displays a cross initially. A click changes it to a
                        tick. A second click changes it back to a cross.

(b)     Null/tick       Similar to (a), but initial state is an empty box.

(c)     Null/star       Similar to (b), but second state is a star.

(d)     Null/tick/cross This is a three state check box the first state
                        being an empty box.

                        Repeated clicking cycles through tick, cross and
                        back to empty box.

(e)     ?/tick/cross    Another three-state check-box where the first is a
                        question mark.

Check-boxes provide the fastest way of entering true/false or yes/no type
data and the 3-state types allow for yes/no/undecided situations. If you
examine the ValStrings file inside !Powerbase.Resources you will find
strings associated with each of these five types the latter parts of which
read, respectively:

        QNo,Yes     Q-,Yes      Q-,*     QNo,Yes,-     QNo,Yes,?

These specify what will actually appear in a print-out when a check-box
field is included in a query (see 3.3). You may change them if you wish, but
don’t omit the initial Q (although this will not appear in the print-out)
and take care not to alter other parts of the string.

4.2.8   External fields

External fields allow you to link Powerbase records to pieces of data of a
size and type which make them unsuitable for inclusion in an Editable field.
Such items are sometimes called “BLOBs” (Binary Large Objects) in the PC
world. The field types in this class and the types of data linked to them
are as follows:

(a)     Text            Plain text files, such as Edit creates.
(b)     Sprite          Sprite files, such as Paint creates.
(c)     Draw            Drawings such as Draw creates.
(d)     Text block      Plain text files, as for (a).
(e)     Picture         Sprite files, as for (b).
(f)     Remote          Anything!

When you create a field of type Text, Sprite or Draw it appears on the
record window as a button bearing a small version of the icon for Edit,
Paint or Draw respectively. Files of the appropriate type may be dropped on

                                - 30 -

these buttons, whereupon the file is copied into a special system of
subdirectories within the database application. Unlike fields of the
Editable class the data doesn’t become part of the Database file within the
application (see also 4.2.6); the Text, Sprite or Draw file retains its
identity and may be exported for editing in the appropriate application.
Clicking on the button in the record window will display the file if the
filer knows the whereabouts of the relevant editor (Edit, Paint or Draw).

Text block and Picture fields take things a step further by actually
displaying a text or sprite file on the record window. The bounding box of
the icon needs to be of suitable size to hold the text or sprite. In the
case of a Text block too small a box will cause the text to appear
truncated. None is actually lost; it just can’t all be displayed. Too small
a box for a Picture field will cause the sprite to spread beyond its
boundaries. (N.B. To display the same sprite on each record, e.g. a company
logo, define the field as of type Logo, not Picture.)  the contents of a
Text block or Picture field can be loaded into Edit or Paint by
double-clicking with SELECT.  When the edited text or sprite is saved the
Powerbase field will be seen to update.

Remote fields allow any type of filer object (file, directory or
application) to be linked to a button on the record window. They differ from
Text, Sprite, and Draw buttons in that the linked object is not copied into
the database directory; only its pathname is stored, hence the term
“Remote”. This has both advantages and disadvantages. Because no copy is
made disc space is saved and the database stays a manageable size; an
important factor where large sprites are involved.  On the other hand, if
you transfer the database to another computer all references to the linked
objects become invalid and might even cause retrieval of files which are
quite different from the ones you originally attached to the records!
Similarly, if you move a linked object to some other part of your hard disc
the pathname stored by Powerbase will no longer be correct.

A Remote button normally shows a large, down-pointing arrow inviting the
user to drop an object onto it. It will accept files of any type, ordinary
directories or applications. Once a link is established the button shows the
appropriate file icon or a blue folder or the default application icon. A
single click has the same effect as double-clicking the object in a filer
window: directories are opened, applications are run, files are loaded into
their applications, Obey files and Basic programs are executed. If the
linked object can’t be found Powerbase will be aware that something used to
be there and the button will display a question mark.

If you wish to break the link between button and file object click MENU over
the button, go to the Field submenu and choose Unlink object.  The button
will revert to its original down-pointing arrow icon.

The uses of this field type are limitless. You can use it to catalogue your
collection of GIFs, JPEGs, sound samples, Sibelius files, Impression
documents or whatever. You could even use it as an application launcher,
although I’m sure there are more convenient ones available!

The full pathname of a Remote object is stored in the database. Since RISC
OS allows pathnames up to 255 characters long they can occupy rather a lot
of space in a large database. Powerbase does allow you to specify a maximum
pathlength of less than 255 characters but you must be very careful when
using it. The value is set in the !Powerbase.Resources.Config file and is
called PathLen. When an empty database is created it will use whatever value
of PathLen is currently configured and it may not subsequently be changed
without a complete reformat of the database. Be wary, therefore, about
reducing the default value of 255 characters. Since, however, it is now
possible for databases to have Config files of their own it would be far
better to place any lower value of PathLen in such a file and leave
Powerbase’s default at 255. Any attempt to store an overlong pathname in a
Remote field will be rejected with an error message.

4.2.9   Computed fields

Discussion of Computed fields is postponed to Ch 6 − Performing
Calculations.

4.2.10  Stamp fields

Stamp fields resemble Computed fields in that you cannot edit them;
Powerbase “stamps”  the fields with the appropriate contents automatically.
The following types are available:

(a)     Record number   Database record number.

(b)     Sequence number Unique value numbered upwards from base value set by
                        user.

(c)     Time            Time at which record was created.

(d)     Date            Date on which record was created.

(e)     Date and time   Date and time of record creation.

(f)     Day             Day on which record was created (in the form Sun,
                        Mon etc or 1,2 etc. or day of month as 10, 24 etc).

                                - 31 -

(g)     Month           Month in which record was created (as either a
                        string; Jan, Feb etc or a number; 1, 2 etc).

(h)     Year            Year when record was created as four-digit number,
                        e.g. 1993

(i)     Last altered    Records the date and time record is first created
                        and updates it only

                        if the record is subsequently altered. Merely
                        displaying the record does not cause updating.

(j)     Logo            Allows a sprite to be included as a logo on  every
                        record.

These fields are stamped by Powerbase when a record is first entered and
thereafter, with the exception of (i), stay fixed. (But see Ch.6 for similar
types of field which auto-update.) Note the following:

•       When defining Record or Sequence number fields make sure the Data
        length icon contains a large enough value to accommodate the longest
        number which will be encountered. For the other types the required
        field length is already known by Powerbase and the Data length box
        is therefore shaded.

•       The base value from which sequence numbers begin is entered in the
        Numeric min box. When a record containing such a field is deleted
        the sequence number is not normally re-used; a new record is given a
        new sequence number.  This, of course, leads to gaps in the numeric
        sequence and you might want to reassign the numbers so that the
        sequence is continuous.  You can do so by means of Compact sequence
        from the Field submenu. Before this can be used the sequence number
        field must be indexed and selected as the current index.

•       Date fields may display the date in any of three formats:

        (1)     Sun,01 Aug 1993 (called “Long date”)
        (2)     01-08-93        (called “Date 8”)
        (3)     01-08-1993      (called “Date 10”)

(2) and (3) are identical to the formats in which Powerbase displays dates
of the ordinary Editable type (see 4.2.5). The numbers refer to the field
length occupied by the date stamp.

•       Logo fields require the name of the sprite to be entered as the tag
        of the field to be used as a logo. This means that the sprite name
        is limited to four characters. The icon for a Logo field (like that
        of a Picture field) must be large enough to hold the intended
        sprite. You may have several logo fields on your record, all the
        required sprites being included in a sprite file called UsrSprites
        which is placed inside the database directory.

4.2.11 Button fields

Any or all of the control buttons on the Powerbase keypad may be made to
appear on the record window itself. They have exactly the same functions as
the keypad equivalents. These button fields let you build a customised
database which allows the user to use only the features you want him/her to
have access to since the keypad and menus can then be suppressed (see 11.2).
There is also a group of Extra buttons which have no keypad equivalent:

Print brings up the Match window for report printing. Print button (on
screen)

Exit duplicates the action of Close database on the iconbar menu. Exit
button

Quit duplicates the action of Quit on the iconbar menu. Quit button  Run
file button, unlinking

Run file. This type of button displays an icon made up of four different
filetype icons. When you drop a file onto the button the icon changes to
that of the relevant file and subsequent clicks on the button will run the
file. Text files are treated as Powerbase scripts (see Ch 12), other types
of file (e.g. Obey files) have their normal Run action. It is important to
appreciate the difference between this type of button and a Remote field
button (see 4.2.8). The file associated with a Remote field belongs to a
specific record and every record can have a different file. The file
attached to a Run file button is the same whatever record is being displayed
and merely provides a convenient means of executing the file.  To break the
link with the file click MENU over the button, go to the Field submenu and
choose Unlink file. The name of the file, if required, may be given in the
descriptor or the leafname may be made to appear on the button by editing
the Config file.

                                - 32 -

Directory. This resembles the previous type. In its initial state the button
displays the small directory icon. Dropping a directory onto it changes this
to a large directory icon and clicking on the button opens a filer window on
the directory.  To break the link with the directory click MENU over the
button, go to the Field submenu and choose Unlink directory. Options in the
Config file allow you to determine what the filer window looks like; the
default being small icons and alphabetic sorting. The leafname may be
displayed on the button. Without it you could confuse Directory and Run file
buttons with Remote fields.

Menu. This button lets you associate a data field with a pop-up menu.
Clicking the button and choosing a character string from the menu enters it
into the data field. The field number of the data field is entered as the
tag of the menu button. This happens automatically if you define the button
immediately after the data field.  The menu data is in a text file whose
name is the tag of the associated data field plus the word “Menu”. Put the
menu title on the first line of the file and each menu choice on a separate
line.  All user-menu files are stored in the Menus subdirectory inside the
database directory. If you click on a Menu button without having constructed
the relevant menu file you will be given appropriate instructions.

4.2.12 Mandatory fields

This isn’t yet another class of fields. A mandatory field is one which must
be filled in before you can access another record or close the database. It
has already been noted that at least one of the primary key fields must not
be blank (see 2.4.1), but any Editable field (but not Scrollable list) can
be made mandatory by selecting the Must not be blank option button when
designing the field. Such fields appear on a working database with red as
the foreground colour. (This may be altered via the Colours window; see
14.5)

4.3     A short-cut to a working database
-----------------------------------------
The third item on the New database menu is called Default database. Choosing
this is by far the quickest way of getting a database up and running. Its
action is to create three files inside the application directory. These are
called Form (which holds the record design), PrimaryKey and Database (which
will ultimately contain the entered records). The number of records in the
database is set to 100, with 25 as the amount by which this should increase
when the database becomes full. The primary key is defined as the first four
characters of the first Editable field. The database is opened and a blank
record displayed ready for data entry.  Since you can always alter such
things as the database size and primary key structure later, you might wish
to use these defaults while you experiment with the database.

If you want to choose the database size and primary key structure yourself
follow the procedure in Sections 4.4 and 4.5

4.4     Specifying the database size
------------------------------------
This involves two steps:

(1)     Save the Form file (which contains all the field data). The Save
        form file menu entry leads to a standard Save box but, since the
        pathname is correctly set for saving the file inside your database
        application, all you actually need do is click on the menu item
        itself.

(2)     You will now see that the Database size choice is no longer shaded
        and may be used to reach the Size window in which you specify the
        number of records in the database and the increment for expanding
        the database when it becomes full.

4.5     Specifying the primary key
----------------------------------
4.5.1   General procedure

Click MENU and choose Primary key.  This opens the Key Structure window. The
primary key (or any other key) is derived from one or more record fields
called key fields. Up to four key fields may be used to define a key but we
will begin by using just one.  Four pieces of information need to be
specified:

(1)     Choose the field, either by clicking on the bump icons or choosing
        from the associated pop-up menu.  The default is the first Editable
        field in the record.

(2)     Enter the word within the field from which characters are to be
        taken to make up the key.  The default is word 1.  If 0 is entered
        word boundaries are ignored (see 4.5.2).

(3)     Enter the position within the word from which characters are to be
        taken. L (default) means from start of word, R means take from end.
        A number (n) means start at the nth character.

                                - 33 -

(4)     Enter the number of characters to be taken from the word. The
        initially-set value is 4 but thereafter the default is the defined
        field length.

After entering this information click on Create and the empty Database and
PrimaryKey files will be created, after which the database will open ready
for you to start entering records.

4.5.2   Some illustrative examples

Key fields should be chosen with care. An ideal key field is one whose
contents would never be repeated in another record. Powerbase lets you
enforce this condition if you wish (see 11.2.1) but the default setting
allows key duplication. Occasional repetitions may not be serious, but a
field which can have only a few “values” is usually a poor choice. A
customer number or membership number is the sort of thing we are looking for
but your database may not contain anything like that.  In a database of
school pupils the pupil’s name would be a good choice of key, but the form
teacher’s name would not, since only a small number of names would be
involved, each appearing on the record of many pupils.

Suppose you decide to use a person’s name, stored surname first, in a single
field whose tag is NAME, as a key. You could use the whole name but it would
probably be too long. The first four letters would be more appropriate but,
as this is the start of the surname, you might get a lot of duplication.
e.g. PRESTON and PRESCOTT would both have the key PRES; and this is before
we even consider people whose surnames are identical. To get round this
problem, Powerbase lets you construct an alphanumeric key from characters
taken from up to four successive words. You will seldom need to go so far.
In the present case a five-letter key made up from the first four letters of
the surname and one letter of the forename would be good enough for most
purposes.

Set up the Key Structure window as follows:

                        Field   Word            Position        Chars

        1st row:        NAME    1               L               4
        2nd row:        NAME    2               L               1

This works quite well.  Smith Peter and Smith Janet would have the keys
SMITP and SMITJ. Duplication can still occur, but not often enough to be a
serious problem.

Further examples illustrate the use of letters from different parts of words
in the key field.  The following settings of word, position and characters
would produce the keys shown from the name Herring Albert.  (a)­(d) use the
Surname only, (e) and (f) use the Forename only, the rest use both names:

        From Surname             From Forename

     Word   Pos   Chars      Word   Pos   Chars   Key

(a)   1      L      5         -      -      -     HERRI
(b)   1      R      4         -      -      -     RING
(c)   1      2      3         -      -      -     ERR
(d)   1      4      6         -      -      -     RING    (stops at word end)
(e)   -      -      -         2      L      3     ALB
(f)   -      -      -         2      R      4     BERT
(g)   1      L      4         2      L      4     HERRALBE
(h)   2      L      3         1      R      3     ALBING
(i)   1      4      3         2      4      2     RINER
(j)   0      R      7         -      -      -     GALBERT (ignores breaks)
(k)   -      4      6         -      -      -     RINGAL  (ignores breaks)

Take particular note of what happens if the word number is entered as 0 (or
left blank).  All breaks between words are then ignored.  The field is
treated as if the blanks between words had been removed leaving a single
long word which is then subjected to the process specified by the position
and characters icons.  When, on the other hand, the word number is 1 or
greater the scanning for characters stops when the end of the word is
reached so that the key might be shorter than the character length
specified. Example (d) illustrates this.

4.5.3   Using more than one field in a key

You might, of course, want to store surname and forename in different
fields. Let’s call their tags SNAM and FNAM.  To produce the five-letter
keys given in our first example the key definition would be:

                                - 34 -

                        Field   Word        Position          Chars

        1st row:        SNAM    1               L               4
        2nd row:        FNAM    1               L               1

and the keys generated will be exactly the same as they would be for the
single field NAME.

There are databases where no single field is suitable for constructing the
primary key.  In a database of classical music, for example, there would
probably be one field for the composer’s name and one for the name of the
work. Neither is much use individually; the former could contain many
occurrences of Mozart or Beethoven and the latter many repetitions of
Symphony No. 5 or String Quartet in D minor.

This is the kind of situation where you might want to use two fields and as
many as four words, taking one word from the Composer field and three from
the Work field using, say, 4, 3, 3 and 2 characters respectively from them.
The following two works then give the keys shown (characters taken from the
left in each case):

        BEETHOVEN  Symphony No 5:               BEETSYMNO5
        MOZART Piano concerto 23:               MOZAPIACON23

Note the following points in these examples:

(a)     If a word is shorter than the number of letters assigned to it (“No”
        in the first example) then the whole word is used but no padding is
        inserted.

(b)     It may be necessary to omit an insignificant word (“No” in the
        second example), in order to make a significant one (“23”) come in
        the first four words of the combined key fields.

NOTE It is not usually sensible to build up keys derived from Numeric fields
out of separate bits in this way and, indeed, Powerbase won’t allow you to
do so. Numeric keys should use one key-field only, the key being the actual
numeric value of the field contents.

4.5.4   Other matters concerning keys

We have already noted that a word shorter than the number of characters
specified is used just as it is, resulting in a short key. There might be
times when you want short words padding out with spaces to give a key of
full length and there is a switch to select this action in the Key Structure
window.

A final factor to be decided is whether the indexing is to pay attention to
the case of letters, i.e. if they are capitals or small letters By default
indexing is not case-sensitive. Thus if a record has the word “Horse” as the
contents of a key field and the first four letters are used as the key then
the entry in the index will be “HORS” and you may search for it by entering
“HORS”, “hors”, “Hors”, “hOrS” etc.

This may not be what you want. If you require indexing to be case sensitive
select the switch labelled Case. Forcing to upper case as described above
does not then take place: keys are inserted and strings are searched for “as
is”. The field containing “Horse” will be indexed as “Hors” and only that
precise combination of upper and lower case letters will successfully find
it.

Alphanumeric keys will be ordered according to the ASCII values* of the
letters. Since lower-case letters come later in the ASCII table than
upper-case ones a record containing “dog” would appear after one containing
“Horse”, whereas one containing “Dog” would come before “Horse”.

4.6     Building the empty database
-----------------------------------
Once you are satisfied click Create and blank Database and PrimaryKey files
will be created. The record window is redrawn and you may start entering
data at once.

4.7     Renaming a database
---------------------------
Use the Rename database option on the Miscellaneous submenu to do this.  If
you rename via the filer your database will lose its ‘PB’ icon and revert to
the default application icon (‘APP’ with Archimedes ‘A’, or 2 large
exclamation marks if you have RISC OS 4). Rename database is duplicated on
the iconbar menu so that you can rename at the record-design stage.

                                - 35 -